IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageGetRoutes]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[cms_sp_PageGetRoutes]
GO
create procedure [dbo].[cms_sp_PageGetRoutes]
(
@SiteId int = null,
@SiteGroupId int = null,
@SystemLanguageId int = null,
@EntityTypeParameter nvarchar(400)
)
as
select 
    Pages.PageId,
    Pages.SiteGroupId,
    Pages.SiteId,
    Pages.SystemLanguageId,
    Pages.PublicPageId,
    Pages.PageTypeKey,
    Pages.PageBehaviourTypeKey,
    Pages.PageName,
    Pages.PageUrl,
    Pages.IsVisible,
    Pages.UseFileCache,
    Pages.CreatedBy,
    Pages.DateCreated,
    Pages.LastUpdatedBy,
    Pages.DateLastUpdated
from dbo.Pages
inner join dbo.EntityRevisions
    on EntityRevisions.EntityId = Pages.PageId and EntityRevisions.EntityTypeParameter = @EntityTypeParameter
where EntityRevisions.IsLatestCompletedRevision = 1 and
    EntityRevisions.IsPublished = 1 and
    EntityRevisions.IsDeleted = 0
GO